from flask_sqlalchemy import SQLAlchemy
from flask import Flask

# 初始化数据库连接
from sqlalchemy import create_engine

url = "mysql+pymysql://root:732837@root:732837@localhost:3306/Work?charset=utf8"  # pymysql
# API与数据库交互,添加的charset可防止中文乱码
engine = create_engine(url, echo=False, encoding="utf-8")  # url为配置文件，echo调试参数，值为为true打印整个sql执行过程

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "mysql+pymysql://root:732837@localhost:3306/Work"

app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///" + "/Users/imac/Desktop/course_design.db"

app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

app.config["SECRET_KEY"] = "huieawfhliu"

db = SQLAlchemy(app)


# 学生表
class Student(db.Model):
    __tablename__ = "student"
    id = db.Column(db.Integer, primary_key=True)  # 学号-主键
    name = db.Column(db.String(64), nullable=False)  # 姓名-不能为空
    gender = db.Column(db.Enum("男", "女"), nullable=False)  # 性别-只能填男和女(Enum枚举)
    grade = db.Column(db.Enum("大一", "大二", "大三", "大四", "研究生"), nullable=False)  # 学生年级
    major_id = db.Column(db.Integer, db.ForeignKey("major.id"))

    scores = db.relationship("Score", backref="student") # 联系学生表和成绩表
    resumes = db.relationship("Resume", backref="student")  # 联系简历表和学生表
    courses = db.relationship("Course", secondary="course_to_student", backref="students")


# 简历表
class Resume(db.Model):
    __tablename__ = "resume"
    id = db.Column(db.Integer, db.ForeignKey("student.id"), primary_key=True)  # 学号-主键
    name = db.Column(db.String(64), nullable=False)  # 姓名-不能为空
    length_of_achooling = db.Column(db.Enum("三年制", "四年制", "五年制", ), nullable=False)  # 学制
    region = db.Column(db.String(64))  # 地区
    age = db.Column(db.Integer)  # 年龄
    political_affiliation = db.Column(db.String(64))  # 政治面貌
    gender = db.Column(db.Enum("男", "女"), nullable=False)  # 性别-只能填男和女(Enum枚举)
    reward_and_punishment = db.Column(db.String(1000))  # 奖惩记录
    special = db.Column(db.String(1000))  # 休复转退记录


# 专业表
class Major(db.Model):
    __tablename__ = "major"
    id = db.Column(db.Integer, primary_key=True)  # 专业号-主键
    major_name = db.Column(db.String(64), nullable=False)  # 专业名-不能为空

    students = db.relationship("Student", backref="major")


# 课程表
class Course(db.Model):
    __tablename__ = "course"
    id = db.Column(db.Integer, primary_key=True)  # 课程号-主键
    course_name = db.Column(db.String(30), nullable=False)  # 课程名


# 成绩表
class Score(db.Model):
    __tablename__ = "Score"
    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(db.Integer, db.ForeignKey("student.id"), nullable=False)
    course_id = db.Column(db.Integer, db.ForeignKey("course.id"), nullable=False)  # 课程号-主键
    term = db.Column(db.Enum("上学期", "下学期"), nullable=False)  # 上下学期
    score = db.Column(db.Integer)  # 分数


# 中间表（学生表-课程表）
class CourseToStudent(db.Model):
    __tablename__ = "course_to_student"
    id = db.Column(db.Integer, primary_key=True)
    student_id = db.Column(db.Integer, db.ForeignKey("student.id"))
    course_id = db.Column(db.Integer, db.ForeignKey("course.id"))


if __name__ == '__main__':
    db.create_all()
